#! /bin/bash


#################### 功能 #########################
# SQL 语法检查
# 备份表
# 按条件备份
# 无锁执行 alter 语句 基于 pt-osc
# 导出查询结果
############### 支持的语法  ########################
# inser, delete, update, selete
# create table, drop table, alter table
# create index, drop index
#    
##################################################


######### mysql 连接信息 #########
MYSQL_USER=dba                     # mysql 连接用户
MYSQL_PASSWORD='123456'            # mysql 连接密码
MYSQL_HOST=127.0.0.1               # mysql 连接地址
MYSQL_PORT=3306                    # mysql 连接端口
MYSQL_DATABASE=test              # mysql 数据库

# 主库创建用户
# create user pt_tools@'10.10.8.62' identified by '123456'; grant all on *.* to pt_tools@'10.10.8.62';flush privileges;

#########     常用配置   #########
BACKUP_PATH=/root/dba/bak          # 备份保存路径
MYSQL_TAG='10.10.8.63'             # mysql 报警和日志显示的主机名或标签
ROWS_SUM_PROMPT=10                 # 行数大于该值 则提示是否继续 
MYSQL_INPUT_FILE=./1_sql_info.txt  # 待执行 sql 写入此文件
LOG_SAVE_DAYES=30                  # 日志保存时间/天
BACKUP_SAVE_DAYES=90               # 备份保存时间/天

######### pt-osc 账号密码 #########
PTOSC_HOST=10.10.8.62              # pt-osc 连接地址
PTOSC_PORT=3306                    # pt-osc 连接端口
PTOSC_USER=ptosc                   # pt-osc 连接用户
PTOSC_PASSWORD='123456'            # pt-osc 连接密码

PTOSC_SLAVE_USER=ptosc             # pt-osc 连接 slave 用户名
PTOSC_SLAVE_PASSWORD='123456'      # pt-osc 连接 slave 密码

######### pt-osc 参数 无特殊情况 保持默认即可 #########
PTOSC_pause_file='/tmp/ptosc_pause.txt'                    # 当指定的文件存在时 暂停执行
PTOSC_max_load='threads_running=100,threads_connected=200' # 超过指定负载时 暂定执行
PTOSC_critical_load='threads_running=1000'                 # 超过指定负载时 终止执行
PTOSC_chunck_size=1000                                     # 指定每次复制的行数
PTOSC_alter_foreign_keys_method=auto                       # 指定外键更新方式
PTOSC_check_replication_filters=no                         # 是否检查复制过滤器 可选参数: yes/no
PTOSC_check_unique_key_change=no                           # 是否检查唯一索引 可选参数: yes/no
PTOSC_check_slave_lag=no                                   # 是否检查从库延迟 可选参数: yes/no
PTOSC_max_lag=3                                            # 从库最大延迟 单位: 秒
PTOSC_recursion_method=none                                # 查找从库方法 //设置为 none 不查找从库
PTOSC_drop_old_table=yes                                   # 是否删除原始表 可选参数: yes/no

#########    日志输出    #########
LOG_PATH=./logs    # 日志输出路径
DATETIME="$(date '+%Y%m%d%H%M%S')"
WORK_ORDER_LOG="${LOG_PATH}/${DATETIME}_work_order.log"  # 脚本日志输出路径
ALTER_LOG="${LOG_PATH}/${DATETIME}_alter.log"   # pt-osc 工具日志输出路径

#########    默认即可    #########
OUTPUT_PATH=/tmp  # select 查询 .xls 文件输出路径
MYSQL_GHOST_FILE=/tmp/.mysql_ghost_dlpefc.txt  # 格式化后读取的文件 无需修改

#########    命令路径    #########
MYSQL_COMMAND_PATH="$(/usr/bin/which mysql)"                                                            # mysql 命令路径 默认即可
MYSQLDUMP_COMMAND_PATH="$(/usr/bin/which mysqldump)"                                                    # mysqldump 命令路径 默认即可
PTOSC_COMMAND_PATH="$(/usr/bin/which  pt-online-schema-change 2>/dev/null)"                             # pt-online-schema-change 命令路径
MYSQL_CONN="$MYSQL_COMMAND_PATH -u${MYSQL_USER} -h${MYSQL_HOST} -P${MYSQL_PORT} -p${MYSQL_PASSWORD} "   # mysql 连接参数拼接
MYSQLDUMP_CONN="${MYSQLDUMP_COMMAND_PATH} -u${MYSQL_USER} -p${MYSQL_PASSWORD} -h${MYSQL_HOST} -P${MYSQL_PORT} --no-create-info --single-transaction --skip-extended-insert " # mysqldump 连接参数

##################################################################
# 写日志
log_message() {
    # log_message "测试信息" "error/info" 0/1
    local message_content="$1"
    local message_status="$2"
    local message_type=$3
    local timestamp="$(date '+%Y-%m-%d %H:%M:%S')"

    case $message_type in
    0)# 正常显示

        printf "%-s %-s %-6s %-s\n" "$timestamp" "$MYSQL_TAG:$MYSQL_PORT" "$message_status" "$message_content"  >>${WORK_ORDER_LOG}
        printf "%-s %-s %-6s %-s\n" "$timestamp" "$MYSQL_TAG:$MYSQL_PORT" "$message_status" "$message_content" 
        ;;
    1)# 格式化显示
        
        printf "%-s %-s %-6s %-s\n" "$timestamp" "$MYSQL_TAG:$MYSQL_PORT" "$message_status" "$message_content"  >>${WORK_ORDER_LOG}
        printf "%-s %-s %-6s %-s\n" "$timestamp" "$MYSQL_TAG:$MYSQL_PORT" "$message_status" "$message_content"
        ;;
    *)# 其他的报错
        echo "$(date '+%Y-%m-%d %H:%M:%S') 日志类型错误 请检查报错"  >>${WORK_ORDER_LOG}
        echo "$(date '+%Y-%m-%d %H:%M:%S') 日志类型错误 请检查报错"
        ;;
    esac
}

# 检查 mysql 连接
check_mysql_conn() {
    # check_mysql_conn
    $MYSQL_CONN -N -e 'use ${MYSQL_DATABASE};select 1;' &>/dev/null
    if [ $? -ne 0 ];then
        log_message "MySQL 连接失败 请检查连接信息及指定库" "error" 0
        echo 3
    else
        echo 1
    fi
}

# 检查文件或目录是否存在
check_path() {
    # check_path "/tmp/" "f/d"
    # 不存在:2 / 存在:1 / 类型输出错误:3
    local check_path="$1"
    local check_type="$2"

    case $check_type in
    f)# 文件检查
        if [ ! -f "$check_path" ];then
            echo 2
        else
            echo 1
        fi 
        ;;
    d)# 目录检查
        if [ ! -d "$check_path" ];then
            echo 2
        else
            echo 1
        fi 
        ;;
    *)# 输入错误
        echo 3
        ;;
    esac 

}

# 检查网络
check_network() {
    # check_network "127.0.0.1"
    local check_ip="$1"
    ping -c 1 ${check_ip}  >/dev/null
    if [ $? -eq 0 ]; then
        echo 2
    else
        echo 1
    fi
}

# 格式化 sql
format_sql() {
    # format_sql 直接执行即可
    # 检查是否为备份表
    local check_backup_table=$(egrep -v '^$|^#|^\s*#|^\s*--' ${MYSQL_INPUT_FILE}|egrep -ic '^(`)?[a-zA-Z0-9_]*(`)?$')
    
    if [[ ${check_backup_table} -eq 0 ]];then
    # 备份表语法去除多余内容
        #cat ${MYSQL_INPUT_FILE}|egrep -v '^$|^#|^\s*#^|\s*--'  |awk -F ';'  BEGIN{RS=EOF}'{gsub(/\n/," ");print}' |awk 'BEGIN{i=1}{gsub(/;/,";""\n");i++;print}'|sed 's/^[[:space:]]*//'|grep -v '^$' >${MYSQL_GHOST_FILE}
        cat ${MYSQL_INPUT_FILE}|egrep -v '^$|^#|^\s*#|^\s*--'|egrep '^\s*`?[a-zA-Z0-9()]' |awk 'BEGIN { RS=";\n"; ORS=";\n\n" } { gsub(/\n/, " "); print }'|egrep '^\s*`?[a-zA-Z0-9()]'  >${MYSQL_GHOST_FILE}
    else
    # sql 语句格式化
        cat ${MYSQL_INPUT_FILE}|egrep -v '^$|^#|^\s*#|^\s*--'|sed -n 's/\s*\([a-zA-Z0-9_]*\)\s*/\1/Ip'|sed 's/`//g' >${MYSQL_GHOST_FILE}  
    fi
}


# 将 create index 改成 alter table 语句
sql_change_create_index() {
    # sql_change_create_index "sql"
    local sql_info="$1"
    local table_name="$(echo "$sql_info"|sed -E -n 's#CREATE\s+(INDEX|UNIQUE\s+INDEX)\s+(\w+)\s+ON\s+(`?[a-zA-Z0-9_]+`?\.)?(`?[a-zA-Z0-9_]+`?)\s*(.*);#\4#pI'| sed 's/`//g' )"
    local index_name="$(echo "$sql_info"|sed -E -n 's#CREATE\s+(INDEX|UNIQUE\s+INDEX)\s+(\w+)\s+ON\s+(`?[a-zA-Z0-9_]+`?\.)?(`?[a-zA-Z0-9_]+`?)\s*(.*);#\2#pI'| sed 's/`//g' )"
    local index_type="$(echo "$sql_info"|sed -E -n 's#CREATE\s+(INDEX|UNIQUE\s+INDEX)\s+(\w+)\s+ON\s+(`?[a-zA-Z0-9_]+`?\.)?(`?[a-zA-Z0-9_]+`?)\s*(.*);#\1#pI'| sed 's/`//g' )"
    local index_column="$(echo "$sql_info"|sed -E -n 's#CREATE\s+(INDEX|UNIQUE\s+INDEX)\s+(\w+)\s+ON\s+(`?[a-zA-Z0-9_]+`?\.)?(`?[a-zA-Z0-9_]+`?)\s*(.*);#\5#pI'| sed 's/`//g' )"
    echo "alter table ${table_name} add ${index_type} ${index_name}${index_column};"
}

# 将 drop index 改成 alter table 语句
sql_change_drop_index() {
    # sql_change_drop_index "sql"
    local sql_info="$1"
    local table_name="$(echo "$sql_info"|sed -E -n 's#drop\s+(INDEX|UNIQUE\s+INDEX)\s+(\w+)\s+ON\s+(`?[a-zA-Z0-9_]+`?\.)?(`?[a-zA-Z0-9_]+`?)\s*(.*);#\4#pI'| sed 's/`//g' )"
    local index_name="$(echo "$sql_info"|sed -E -n 's#drop\s+(INDEX|UNIQUE\s+INDEX)\s+(\w+)\s+ON\s+(`?[a-zA-Z0-9_]+`?\.)?(`?[a-zA-Z0-9_]+`?)\s*(.*);#\2#pI'| sed 's/`//g' )"
    local index_column="$(echo "$sql_info"|sed -E -n 's#drop\s+(INDEX|UNIQUE\s+INDEX)\s+(\w+)\s+ON\s+(`?[a-zA-Z0-9_]+`?\.)?(`?[a-zA-Z0-9_]+`?)\s*(.*);#\5#pI'| sed 's/`//g' )"
    echo "alter table ${table_name} drop index ${index_name};"

}

# 获取 SQL 类型
get_sql_type() {
    # get_sql_type "sql info"
    local sql_info="$1"

    # 增删改查
    local SELECT_TABLE=$(echo ${sql_info}|egrep -ic '^select\s+(.*)\s+from.*;')
    local INSERT_TBALE=$(echo ${sql_info}|egrep -ci '^(insert|REPLACE)\s+into\s+(`)?(\w+)?(`)?(.)?(`)?\w+(`)?\s*(\(.*\))?\s+values\s*(.*)\s*;')
    local DELETE_TABLE=$(echo ${sql_info}|egrep -ci '^delete\s+([a-zA-Z0-9`_, ]+\s*)?from\s+(`?[a-zA-Z0-9_]+`?\.)?(`?[a-zA-Z0-9_]+`?)(.*)where.*;')
    local UPDATE_TABLE=$(echo ${sql_info}|egrep -ic '^update\s+(`?[a-zA-Z0-9_]+`?\.)?(`?[a-zA-Z0-9_]+`?)\s+(join\s+(`?[a-zA-Z0-9_]+`?\.)?(`?[a-zA-Z0-9_]+`?)\s+on\s+.+\s+)?set\s+(.*)\s+where\s+(`)?\w+(`)?.*;')
    
    # 索引创建删除
    local CREATE_INDEX=$(echo ${sql_info}|egrep -ci '^CREATE\s+(UNIQUE|FULLTEXT|SPATIAL)?\s*INDEX\s+\w+\s+ON\s+(`)?(\w+)?(`)?(.)?(`)?\w+(`)?\s*\(\w+\)\s*;')
    local DROP_INDEX=$(echo ${sql_info}  |egrep -ci '^drop\s+INDEX\s+\w+\s+ON\s+(`)?(\w+)?(`)?(.)?(`)?\w+(`)?\s*;')
    
    # 表创建删除
    local CREATE_TABLE=$(echo ${sql_info}|egrep -ic '^create\s+table\s+(\`)?(\w+)?(\`)?(.)?(\`)?\w+(\`)?\s*(.*).*;')
    local DROP_TABLE=$(echo ${sql_info}  |egrep -ic 'DROP\s+TABLE\s+(IF EXISTS )?[a-zA-Z0-9_]+( *, *[a-zA-Z0-9_]+)* *;')
    local TRUNCATE_TBALE=$(echo ${sql_info} |egrep -ic 'truncate\s+table\s+(`?[a-zA-Z0-9_]+`?\.)?(`?[a-zA-Z0-9_]+`?)\s*;')

    # 表修改
    local ALTER_TABLE=$(echo ${sql_info} |grep -iPc '^alter\s+table\s+(`)?(\w+)?(`)?(.)?(`)?\w+(`)?\s+(ADD|MODIFY|DROP|RENAME|ENGINE|CHARSET|AUTO_INCREMENT|COMMENT)?.*;')

    # 备份表
    local BACKUP_TBALE=$(echo ${sql_info}|egrep -ic '^(`)?[a-zA-Z0-9_]*(`)?$')

    if [ $SELECT_TABLE  -ne 0 ];then
        echo 'SELECT_TABLE'
    elif [ $INSERT_TBALE -ne 0 ];then
        echo 'INSERT_TBALE'
    elif [ $DELETE_TABLE -ne 0 ];then
        echo 'DELETE_TABLE'
    elif [ $UPDATE_TABLE -ne 0 ];then
        echo 'UPDATE_TABLE'
    elif [ $CREATE_INDEX -ne 0 ];then
        echo 'CREATE_INDEX'
    elif [ $DROP_INDEX -ne 0 ];then
        echo 'DROP_INDEX'
    elif [ $CREATE_TABLE -ne 0 ];then
        echo 'CREATE_TABLE'
    elif [ $DROP_TABLE -ne 0 ];then
        echo 'DROP_TABLE'
    elif [ $ALTER_TABLE -ne 0 ];then
        echo 'ALTER_TABLE'
    elif [ $TRUNCATE_TBALE -ne 0 ];then
        echo 'TRUNCATE_TBALE'
    elif [ $BACKUP_TBALE -ne 0 ];then
        echo 'BACKUP_TBALE'
    else
        echo 'NULL'
    fi
}

# 获取表名 可能存在多个表
get_table_name() {
    local sql_info="$1"
    local sql_type="$(get_sql_type "$sql_info")"

    # 查询
    local SELECT_TABLE="$(echo "${sql_info}"|awk 'BEGIN{IGNORECASE=1} {for(i=1;i<=NF;i++) if(tolower($i)=="from" || tolower($i)=="join") tables=tables " " $(i+1)} END{print tables}'|sed 's/`//g'|sed 's/ /\n/g'|egrep -v '^$'|awk -F';' '{print $1}'|sort -n |uniq)"

    # 增删改
    local DELETE_TABLE="$(echo "${sql_info}"|awk 'BEGIN{IGNORECASE=1} {for(i=1;i<=NF;i++) if(tolower($i)=="from" || tolower($i)=="join") tables=tables " " $(i+1)} END{print tables}'|sed 's/`//g'|sed 's/ /\n/g'|sort -n|uniq)"
    local INSERT_TBALE="$(echo "${sql_info}"|sed -n 's/\(insert\|replace\)[[:space:]]\+into[[:space:]]\+\([^[:space:]]*\).*values\s*(.*)\s*;/\2/ip'|awk -F'(' '{print $1}'|awk -F'.' '{print $NF}'|sed  's#`##g'|sort -n|uniq)"
    local UPDATE_TABLE="$(echo "${sql_info}"|awk 'BEGIN{IGNORECASE=1} {for(i=1;i<=NF;i++) if(tolower($i)=="update" || tolower($i)=="join") tables=tables " " $(i+1)} END{print tables}'|sed 's/`//g'|sed 's/ /\n/g'|sort -n|uniq)"

    # 修改表
    local ALTER_TABLE="$(echo "${sql_info}"|sed -En 's/^alter\s+table\s+(`?[a-zA-Z0-9_]+`?\.)?(`?[a-zA-Z0-9_]+`?)\s+(ADD|MODIFY|DROP|RENAME|ENGINE|CHARSET|AUTO_INCREMENT|COMMENT)?.*;/\2/Ip'|sed 's/`//g')"

    # 创建删除索引
    #local CREATE_INDEX=$(echo "${sql_info}"|egrep -i 'CREATE\s+(UNIQUE|FULLTEXT|SPATIAL)?\s*INDEX\s+.*\s+ON*\(*\)*'|sed -n "s/create\s\+.*\s*index\s\+.*\s\+ON\s\+\([^ ]*\)\s*(.*).*/\1/Ip"|awk -F. '{print $NF}'|sed 's/`//g')
    #local DROP_INDEX=$(echo "${line}"|egrep -i '^drop\s+INDEX\s+\w+\s+ON\s+(`)?(\w+)?(`)?(.)?(`)?\w+(`)?\s*;'|sed -En 's/^drop\s+index\s+\w+\s+ON\s+(`?[a-zA-Z0-9_]+`?\.)?(`?[a-zA-Z0-9_]+`?).*;/\2/Ip'|sed 's/`//g')

    # 创建删除表
    local DROP_TABLE="$(echo "${sql_info}"|sed -En 's/drop\s+table\s+(IF\s+EXISTS\s*)?(.*);/\2/Ip'|sed 's/\s*CASCADE\s*//g'|sed 's/`//g'|sed 's/,/ /g'|sed 's/ /\n/g'|sort -n|uniq)"
    local CREATE_TABLE="$(echo "${sql_info}"|sed -nE 's/create\s+table\s+(`?[a-zA-Z0-9_]+`?\.)?(`?[a-zA-Z0-9_]+`?)\s*((.*))/\2/Ip'|sed 's/`//g')"
    local TRUNCATE_TBALE="$(echo "${sql_info}"|sed -nE 's/truncate\s+table\s+(`?[a-zA-Z0-9_]+`?\.)?(`?[a-zA-Z0-9_]+`?)\s*;/\2/Ip'|sed 's/`//g')"

    # 备份表
    local BACKUP_TBALE=$(echo ${sql_info}|egrep -i '^\s*(`?[a-zA-Z0-9_]+`?\.)?(`?[a-zA-Z0-9_]+`?)\s*$'|sed 's/[ `]//g'|awk -F'.' '{print $NF}' |sort -n|uniq)

    case $sql_type in
    SELECT_TABLE)
        echo "$SELECT_TABLE"
        ;;
    DELETE_TABLE)
        echo "$DELETE_TABLE"
        ;;
    INSERT_TBALE)
        echo "$INSERT_TBALE"
        ;;
    UPDATE_TABLE)
        echo "$UPDATE_TABLE"
        ;;
    ALTER_TABLE)
        echo "$ALTER_TABLE"
        ;;
    DROP_TABLE)
        echo "$DROP_TABLE"
        ;;
    CREATE_TABLE)
        echo "$CREATE_TABLE"
        ;;
    TRUNCATE_TBALE)
        echo "$TRUNCATE_TBALE"
        ;;
    BACKUP_TBALE)
        echo "$BACKUP_TBALE"
        ;;
    *)
        echo 'NULL'
        ;;
    esac
}

# 获取where条件 基于 where 条件备份
get_condition() {
    # get_condition "sql"
    local sql_info="$1"
    local sql_type="$(get_sql_type "$sql_info")"
    local table_count=$(get_table_name "$sql_info"|egrep  '\s*[a-zA-Z0-9_]'|wc -l)

    # update 语法
    local UPDATE_TABLE="$(echo "$sql_info"|sed -E -n 's/^update\s+(`?[a-zA-Z0-9_]+`?\.)?(`?[a-zA-Z0-9_]+`?)\s+set\s+(.*)\s+where\s+(.*)(limit|group\s+by|order\s+by|desc)?.*;/\4/Ip')"

    # delete 语法
    local DELETE_TABLE="$(echo "$sql_info"|sed -E -n 's/^delete\s+from\s+([`a-zA-Z0-9_.]*)\s+WHERE\s+(.*);/\2/Ip'|sed  's/limit.*$//gI')"

    if [[ $table_count -eq 1 ]];then
        case $sql_type in 
        UPDATE_TABLE)
            echo "$UPDATE_TABLE"
            ;;
        DELETE_TABLE)
            echo "$DELETE_TABLE"
            ;;
        *)
            echo 'NULL'
            ;;
        esac
    else
        echo 'NULL'
    fi
}

# 获取唯一索引列名
get_unique_column_name() {
    # 检查 alter unique 时使用
    # get_unique_column_name "sql_info"
    local sql_info="$1"
    local column_name="$(echo "$sql_info"| egrep -oi  'ADD\s+UNIQUE[^)]*' |awk -F'(' '{print $2}'|sed  's/`//g')"
    echo "$column_name" 
}

# 单条 sql 提取 type/table name/where condition/column name
sql_processing() {
    #sql_processing "sql_type" 'UPDATE employees SET salary = salary * 1.05, title = 'Lead Developer' WHERE department_id = 3 AND hire_date > '2020-01-01';'
    ## 获取类型
    #sql_processing "sql_type" "sql"
    ## 获取表名
    #sql_processing "table_name" "sql"
    ## 获取 where 条件
    #sql_processing "condition" "sql"
    ## 获取列名
    #sql_processing "column_name" "sql"

    local get_type="$1"
    local sql_info="$2"
    
    case $get_type in
    sql_type)
        get_sql_type "$sql_info"
        ;;
    table_name)
        get_table_name "$sql_info"
        ;;
    condition)
        get_condition "$sql_info"
        ;;
    *)
        echo 'NULL'
        ;;
    esac
}

# 检查 mysql 连接
check_mysql_conn() {
    # check_mysql_conn
    $MYSQL_CONN -N -e 'select 1;' &>/dev/null
    if [ $? -ne 0 ];then
        echo 2
    else
        echo 1
    fi
}

# mysql 备份
mysql_backup(){
    # mysql_backup "table_name" "where_info"
    local table_name="$1"
    local where_info="$2"
    local backup_status=0

    if [ "$where_info" != '' ];then
        for table in $(echo ${table_name}|egrep  '\s*[a-zA-Z0-9_`]'|sort -n|uniq); do

            # 
            ${MYSQLDUMP_CONN}  --where="$where_info"  ${MYSQL_DATABASE}  ${table}   >> ${BACKUP_PATH}/${DATETIME}-${MYSQL_DATABASE}.${table}.sql  2>>${WORK_ORDER_LOG} 
            if [ $? -ne 0 ];then
                backup_status=2
            fi
        done
    else
        for table in $(echo ${table_name}|egrep  '\s*[a-zA-Z0-9_`]'|sort -n|uniq); do

            # 2>>${WORK_ORDER_LOG}
            ${MYSQLDUMP_CONN}   ${MYSQL_DATABASE}  ${table}  >> ${BACKUP_PATH}/${DATETIME}-${MYSQL_DATABASE}.${table}.sql 2>>${WORK_ORDER_LOG} 
            if [ $? -ne 0 ];then
                backup_status=2
            fi
        done
    fi


    if [ $backup_status -eq 2 ];then
        echo 2
    else 
        echo 1
    fi
}

# 检查行数
check_rows() {
    # check_rows "table_name" "where info"
    # 有没有 where 条件都能传 函数内判断是否存在 条件
    # 单表 可以使用 where 条件 多表扫描全表
    local table_name="$1"
    local where_info="$2"

    local table_total=$(echo "${table_name}"|egrep  '\s*[a-zA-Z0-9_]'|wc -l)

    if [[ "$where_info" == 'NULL' || "$where_info" == '' ]];then
        where_info=''
    else 
        where_info="where ${where_info}"
    fi

    if [[ $table_total -ne 1 ]];then
        for table in $(echo ${table_name}); do
            local table_rows=$(${MYSQL_CONN} ${MYSQL_DATABASE} -N -e "select count(*) from ${table};" 2>/dev/null)
            local fields=$(${MYSQL_CONN} ${MYSQL_DATABASE} -N -e "desc ${table};" 2>/dev/null |wc -l)
            if [[ $table_rows -gt $ROWS_SUM_PROMPT ]];then
                echo "${table} 表 字段数为 ${fields} 个, 备份的数据量为 ${table_rows} 行"
                read -t 20  -e -p "是否继续(y/n)" Answer
                case $Answer in
                Y | y)
                    echo "确认继续"
                    ;;
                N | n)
                    echo "确认退出"
                    exit 1
                    ;;
                *)
                    echo "输入有误"
                    exit 1
                    ;;
                esac
            fi
        done
    else
        local table_rows=$(${MYSQL_CONN} ${MYSQL_DATABASE} -N -e "select count(*) from ${table_name} ${where_info};" 2>/dev/null)
        local fields=$(${MYSQL_CONN} ${MYSQL_DATABASE} -N -e "desc ${table_name};" 2>/dev/null |wc -l)

        if [[ $table_rows -gt $ROWS_SUM_PROMPT ]];then
            echo "${table} 表 字段数为 ${fields} 个, 备份的数据量为 ${table_rows} 行"
            read -t 20  -e -p "是否继续(y/n)" Answer
            case $Answer in
                Y | y)
                    echo "确认继续"
                    ;;
                N | n)
                    echo "确认退出"
                    exit 1
                    ;;
                *)
                    echo "输入有误"
                    exit 1
                    ;;
                esac
        fi
    fi 
}

# 检查表是否存在
check_table() {
    # check_table "table_name"
    local table_name="$1"
    local check_status=0

    for table in $(echo "$table_name"|egrep  '\s*[a-zA-Z0-9_`]'); do 
        $MYSQL_CONN $MYSQL_DATABASE -N -e "desc ${table};" &>/dev/null 
        if [ $? -ne 0 ];then
            check_status=1
        fi
    done

    if [ ${check_status} -ne 0 ];then
        echo 2
    else
        echo 1
    fi
}

# 添加唯一索引 检查重复数据
check_unique_key_duplicate_data() {
    # check_unique_key_duplicate_data "sql_info"
    local check_status=0
    local sql_info="$1"
    local table_name="$(sql_processing "table_name" "$sql_info")"
    local column_name="$(get_unique_column_name "$sql_info")"
    local check_unique_key=$(echo "$sql_info"|egrep -ic 'unique')
    local primary_key_name="$($MYSQL_CONN ${MYSQL_DATABASE}  -N -e "desc ${table_name};" 2>/dev/null|grep -w 'PRI'|grep -w 'auto_increment'|awk '{print $1}')"

    if [ ! -z $primary_key_name ];then
        if [ $check_unique_key -gt 0 ];then
            for column  in $(echo "$column_name"); do
                local sql_info="START TRANSACTION;SELECT MAX(${primary_key_name}) INTO @max_id FROM ${table_name};SELECT COUNT(*) INTO @total_count FROM ${table_name} WHERE ${primary_key_name} <= @max_id and ${column} is not null ;SELECT COUNT(DISTINCT ${column}) INTO @distinct_count FROM ${table_name} WHERE ${primary_key_name} <= @max_id and ${column} is not null;SELECT @total_count != @distinct_count AS has_duplicates;COMMIT;"
                local check_duplicate_data=$($MYSQL_CONN ${MYSQL_DATABASE} -N -e "$sql_info" 2>/dev/null)
                if [ $check_duplicate_data -ne 0 ];then
                    check_status=1
                fi
            done
        else
            check_status=1
        fi
    else
        check_status=1
    fi
    if [ $check_status -ne 0 ];then
        echo 0
    else
        echo 1
    fi
}

# 检查 alter 语法
check_alter_syntax(){
    # check_alter_syntax "sql_info"
    local sql_info="$1"
    local check_alter_sql=$(echo "$sql_info"|egrep -ic 'alter\s+table\s+(`)?(\w+)?(`)?(.)?(`)?\w+(`)?\s+(ADD|MODIFY|CHANGE|DROP|RENAME|DEFAULT)\s+(COLUMN|PRIMARY\s+KEY|UNIQUE|INDEX|CONSTRAINT|FOREIGN|TO|CHARACTER)?.*;')
    if [ $check_alter_sql -eq 0 ];then
        echo 0
    else
        echo 1
    fi
}

# 检查 not null 列默认值
check_not_null_default_value(){
    # check_not_null_default_value "sql_info"
    local sql_info="$1"
    local check_status=0
    local check_not_null=$(echo "$sql_info"|egrep -ic 'not\s+null')
    if [ $check_not_null -gt 0 ];then
        local check_default=$(echo "$sql_info"|egrep -ic 'default')
        if [ $check_default -eq 0 ];then
            check_status=1
        fi
    fi

    if [ $check_status -ne 0 ];then
        echo 0
    else
        echo 1
    fi

}


# 检查当前实例和 sql 是否支持使用 pt-osc
shunt_alter() {
    # shunt_alter "sql_info"
    local check_status=0
    local sql_info="$1"

    # 检查 mysql 版本
    check_mysql_version(){
        local mysql_version=$($MYSQL_CONN -N -e "select @@version" 2>/dev/null|awk -F. '{print $1$2}')
        if [ $mysql_version -lt 55 ];then
            check_status=1
        fi
    }
    check_mysql_version

    # 检查表存储引擎
    check_table_engine(){
        local table_name="$(sql_processing "table_name" "$sql_info")"
        local table_engine="$($MYSQL_CONN ${MYSQL_DATABASE}   -N -e "SELECT ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = '$MYSQL_DATABASE' AND TABLE_NAME = '$table_name';" 2>/dev/null)"
        if [ "$table_engine" != "InnoDB" ];then
            log_message "${table_name} 表的存储引擎为 ${table_engine}" 'warning' 0
            check_status=1
        fi  
    }
    check_table_engine

    # 检查主键或唯一键
    check_table_unique_or_primary_key(){
        local table_name="$(sql_processing "table_name" "$sql_info")"
        local table_unique_or_primary_key="$($MYSQL_CONN  ${MYSQL_DATABASE} -N -e  "SELECT CONSTRAINT_TYPE FROM information_schema.TABLE_CONSTRAINTS WHERE TABLE_SCHEMA = '$MYSQL_DATABASE'  AND TABLE_NAME = '$table_name'  AND (CONSTRAINT_TYPE = 'PRIMARY KEY' OR CONSTRAINT_TYPE = 'UNIQUE');" 2>/dev/null)"
        if  [ -z "$table_unique_or_primary_key" ];then
            log_message "$table_name 表没有主键或唯一键" "warning" 0
            check_status=1
        fi
    }
    check_table_unique_or_primary_key
    

    # 检查触发器
    check_trigger() {
        # 5.7.2之前表上有触发器无法使用 pt-osc
        local table_name="$(sql_processing "table_name" "$sql_info")"
        local check_mysql_version="$($MYSQL_CONN -N -e "select SUBSTRING_INDEX(VERSION(), '-', 1);" 2>/dev/null |awk -F'.' '{printf "%d%d%02d\n", $1,$2,$3}' )"
        if [ $check_mysql_version -lt 5702 ];then
            local sql_info="SELECT COUNT(*) FROM information_schema.triggers WHERE event_object_schema = '$MYSQL_DATABASE' AND event_object_table = '$table_name';"
            local check_trigger=$($MYSQL_CONN -N -e "$sql_info" 2>/dev/null)
            if [ $check_trigger -gt 0 ];then
                log_message "5.7.2 之前版本表包含触发器 无法使用 pt-osc: $sql_info" "warning" 1
                check_status=1
            fi  
        fi
    }
    check_trigger

    # 检查删除外键
    check_drop_foreign_key() {
        local check_drop_foreigin_key=$(echo "$sql_info"|egrep  -ic 'drop\s+foreign')
        if [[ $check_drop_foreigin_key -gt 0 ]];then
            check_status=1
        fi
    }
    check_drop_foreign_key

    # 检查重命名表
    check_rename_table(){
        local check_rename_table=$(echo "$sql_info"|egrep  -ic 'rename\s+to')
        if [ $check_rename_table -gt 0 ];then
            check_status=1
        fi
    }
    check_rename_table

    # 等于 1 可以使用 pt-osc
    if [ $check_status -ne 0 ];then
        echo 0
    else
        echo 1
    fi
}

# pt-osc 工具执行
exec_ptosc() {
    # exec_ptosc "sql_info"
    local sql_info="$1"
    local alter_number=$2

    local table_name="$(sql_processing "table_name" "$sql_info")"
    local alter="$(echo "$sql_info"|sed -nE 's/alter\s+table\s+(`?[a-zA-Z0-9_]+`?\.)?(`?[a-zA-Z0-9_]+`?)\s+(.*);/\3/Ip')"
    local charset="$(${MYSQL_CONN} ${MYSQL_DATABASE} -N -e "show create table ${table_name};" 2>/dev/null |sed -nE 's/.*create\s+table\s+`\w+`\s+\(.*\)\s+.*\s+CHARSET=(.*).*/\1/Ip')"

    if [ "$PTOSC_check_replication_filters" == 'yes' ];then
        PTOSC_check_replication_filters='--check-replication-filters'
    elif [ "$PTOSC_check_replication_filters" == 'no' ];then
        PTOSC_check_replication_filters='--no-check-replication-filters'
    else
        PTOSC_check_replication_filters=''
    fi


    if [ "$PTOSC_check_unique_key_change" == 'yes' ];then
        PTOSC_check_unique_key_change='--check-unique-key-change'
    elif [ "$PTOSC_check_unique_key_change" == 'no' ];then
        PTOSC_check_unique_key_change='--no-check-unique-key-change'
    else
        PTOSC_check_unique_key_change=''
    fi

    if [ "$PTOSC_check_slave_lag" == 'yes' ];then
        PTOSC_check_slave_lag="--check-slave-lag --max-lag=${PTOSC_max_lag}"
    elif [ "$PTOSC_check_slave_lag" == 'no' ];then
         PTOSC_check_slave_lag='--skip-check-slave-lag '
    else
        PTOSC_check_slave_lag=''
    fi

    if [ "$PTOSC_drop_old_table" == 'yes' ];then
        PTOSC_drop_old_table='--drop-old-table'
    elif [ "$PTOSC_drop_old_table" == 'no' ];then
        PTOSC_drop_old_table='--no-drop-old-table'
    else
        PTOSC_drop_old_table=''
    fi


    local ptosc="${PTOSC_COMMAND_PATH} \
    --print --statistics \
    --progress time,10 \
    --preserve-triggers \
    --pause-file=${PTOSC_pause_file} \
    u=${PTOSC_USER},p=${PTOSC_PASSWORD},h=${PTOSC_HOST},P=${PTOSC_PORT},D=${MYSQL_DATABASE},t=${table_name} \
    --alter-foreign-keys-method=${PTOSC_alter_foreign_keys_method} \
    --chunk-size=${PTOSC_chunck_size} \
    --max-load=${PTOSC_max_load} \
    --critical-load=${PTOSC_critical_load} \
    ${PTOSC_check_replication_filters} \
    ${PTOSC_check_unique_key_change} \
    ${PTOSC_check_slave_lag} \
    ${PTOSC_drop_old_table} \
    --recursion-method=${PTOSC_recursion_method} \
    --charset=${charset} \
    "

    # 测试执行
    $ptosc --alter="${alter}"   --dry-run >>${LOG_PATH}/${DATETIME}_${alter_number}_alter.log 2>&1
    if [ $? -eq 0 ];then
        # 正式执行
        nohup $ptosc --alter="${alter}"  --execute >>${LOG_PATH}/${DATETIME}_${alter_number}_alter.log 2>&1 &
        echo 1
    else
        echo 0
    fi

}
########## 逻辑部分函数 ##########

# 执行前检查
# 文件或目录检查
# 连接检查
# sql 类型和行数检查
prepare_exec() {
    local prepare_exec_status=0
    
    log_message "SQL 检查中···" "info" 0
    # 日志路径是否存在
    local check_status=$(check_path "$LOG_PATH" "d")
    if [ $check_status -ne 1 ];then
        log_message "日志路径不存在: ${LOG_PATH}" "error" 0
        prepare_exec_status=1
    fi

    # sql 文件路径是否存在
    local check_status=$(check_path "$MYSQL_INPUT_FILE" "f")
    if [ $check_status -ne 1 ];then
        log_message "备份路径不存在 请检查配置信息" "error" 0
        prepare_exec_status=1
    fi
    

    # 备份路径是否存在
    local check_status=$(check_path "$BACKUP_PATH" "d")
    if [ $check_status -ne 1 ];then
        log_message "备份路径不存在 请检查配置信息" "error" 0
        prepare_exec_status=1
    fi

    # pt-osc 路径是否存在
    local check_status=$(check_path "$PTOSC_COMMAND_PATH" "f")
    if [ $check_status -ne 1 ];then
        log_message "pt-online-schema-change 命令不存在" "error" 0
        prepare_exec_status=1
    fi

    # 检查 mysql 连接
    local check_status=$(check_mysql_conn)
    if [ $check_status -ne 1 ];then
        log_message "MySQL 连接失败 请检查连接信息" "error" 0
        prepare_exec_status=1
    fi

    if [ $prepare_exec_status -eq 1 ];then
        exit 2
    fi

    # 检查是否能获取表名和语法
    while read -u 3 line ;do
        local sql_type="$(sql_processing "sql_type"  "$line")"

        case $sql_type in
        SELECT_TABLE)
            local table_name="$(sql_processing "table_name" "$line")"
            if [ "$table_name" == 'NULL' ];then
                log_message "表名提取失败: $line" "error"  1
                prepare_exec_status=1
            fi

            local table_status=$(check_table "$table_name")
            if [ $table_status -ne 1 ];then
                log_message "表不存在: $line" "error" 0
                prepare_exec_status=1
            fi
            ;;
        INSERT_TBALE)
            local table_name="$(sql_processing "table_name" "$line")"
            if [ "$table_name" == 'NULL' ];then
                log_message "表名提取失败: $line" "error"  1
                prepare_exec_status=1
            fi
            local table_status=$(check_table "$table_name")
            if [ $table_status -ne 1 ];then
                log_message "表不存在: $line" "error" 0
                prepare_exec_status=1
            fi
            ;;
        DELETE_TABLE)
            local table_name="$(sql_processing "table_name" "$line")"
            local where_info="$(sql_processing "condition" "$line")"
            if [ "$table_name" == 'NULL' ];then
                log_message "表名提取失败: $line" "error"  1
                prepare_exec_status=1
            fi
            local table_status=$(check_table "$table_name")
            if [ $table_status -ne 1 ];then
                log_message "表不存在: $line" "error" 0
                prepare_exec_status=1
            fi
            # 检查行数
            check_rows "$table_name" "$where_info"

            ;;
        UPDATE_TABLE)
            local table_name="$(sql_processing "table_name" "$line")"
            local where_info="$(sql_processing "condition" "$line")"
            if [ "$table_name" == 'NULL' ];then
                log_message "表名提取失败: $line" "error"  1
                prepare_exec_status=1
            fi

            local table_status=$(check_table "$table_name")
            if [[ $table_status -ne 1 ]];then
                log_message "表不存在: $line" "error" 0
                prepare_exec_status=1
            fi

            # 检查行数
            check_rows "$table_name" "$where_info"
            ;;
        CREATE_INDEX)
            local alter_sql="$(sql_change_create_index "$line")"
            local table_name=$(sql_processing "table_name" "$alter_sql")
            if [ "$table_name" == 'NULL' ];then
                log_message "表名提取失败: $line" "error"  1
                prepare_exec_status=1
            fi
            local table_status=$(check_table "$table_name")
            if [ $table_status -ne 1 ];then
                log_message "表不存在: $line" "error" 0
                prepare_exec_status=1
            fi
            # 检查行数
            check_rows "$table_name" "$where_info"
            
        # 检查pt-osc

            # 检查重复数据
            local check_unique_index=$(echo "$line"|egrep -ic 'unique')
            if [ ${check_unique_index} -ne 0 ];then
                local check_status=$(check_unique_key_duplicate_data "$line")
                if [ $check_status -ne 1 ];then
                    log_message "索引列存在重复数据: ${line}" "error" 1
                    prepare_exec_status=1
                fi
            fi
            
            # 检查 alter 语法
            local check_status=$(check_alter_syntax "$alter_sql")
            if [ $check_status -ne 1 ];then
                log_message "sql 匹配失败: $alter_sql" "error" 1
                prepare_exec_status=1
            fi
            
            # 检查 not null
            local check_status=$(check_not_null_default_value "$alter_sql")
            if [ $check_status -ne 1 ];then
                log_message "not null 缺少默认值: $alter_sql" "error" 1
                prepare_exec_status=1
            fi

            local check_status=$(shunt_alter "$alter_sql"|tail -n 1|awk '{print $NF}')
            if [ $check_status -ne 1 ];then
                log_message "无法使用 pt-osc工具: $alter_sql" "warning" 1
                read -t 20  -e -p "是否继续(y/n)" Answer
                case $Answer in
                Y | y)
                    echo "确认继续"
                    ;;
                N | n)
                    echo "确认退出"
                    exit 1
                    ;;
                *)
                    echo "输入有误"
                    exit 1
                    ;;
                esac
            fi
            ;;
        DROP_INDEX)
            local alter_sql="$(sql_change_drop_index "$line")"
            local local table_name=$(sql_processing "table_name" "$alter_sql")
            if [ "$table_name" == 'NULL' ];then
                log_message "表名提取失败: $line" "error"  1
                prepare_exec_status=1
            fi
            local table_status=$(check_table "$table_name")
            if [ $table_status -ne 1 ];then
                log_message "表不存在: $line" "error" 0
                prepare_exec_status=1
            fi
            # 检查行数
            check_rows "$table_name" "$where_info"

            ;;
        CREATE_TABLE)
            local table_name="$(sql_processing "table_name" "$line")"
            if [ "$table_name" == 'NULL' ];then
                log_message "表名提取失败: $line" "error"  1
                prepare_exec_status=1
            fi
            local table_status=$(check_table "$table_name")
            if [ $table_status -eq 1 ];then
                log_message "${table_name} 表已存在: $line" "error" 0
                prepare_exec_status=1
            fi
            ;;

        DROP_TABLE)
            local table_name="$(sql_processing "table_name" "$line")"
            if [ "$table_name" == 'NULL' ];then
                log_message "表名提取失败: $line" "error"  1
                prepare_exec_status=1
            fi
            local table_status=$(check_table "$table_name")
            if [ $table_status -ne 1 ];then
                log_message "表不存在: $line" "error" 0
                prepare_exec_status=1
            fi
            ;;

        TRUNCATE_TBALE)
            local table_name="$(sql_processing "table_name" "$line")"
            if [ "$table_name" == 'NULL' ];then
                log_message "表名提取失败: $line" "error"  1
                prepare_exec_status=1
            fi
            local table_status=$(check_table "$table_name")
            if [ $table_status -ne 1 ];then
                log_message "表不存在: $line" "error" 0
                prepare_exec_status=1
            fi
            ;;
        ALTER_TABLE)
            local table_name="$(sql_processing "table_name" "$line")"
            if [ "$table_name" == 'NULL' ];then
                log_message "表名提取失败: $line" "error"  1
                prepare_exec_status=1
            fi
            local table_status=$(check_table "$table_name")
            if [ $table_status -ne 1 ];then
                log_message "表不存在: $line" "error" 0
                prepare_exec_status=1
            fi
            # 检查行数
            check_rows "$table_name" "$where_info"

            # 检查pt-osc

            # 检查重复数据
            local check_unique_index=$(echo "$line"|egrep -ic 'unique')
            if [ ${check_unique_index} -ne 0 ];then
                local check_status=$(check_unique_key_duplicate_data "$line")
                if [ $check_status -ne 1 ];then
                    log_message "索引列存在重复数据: ${line}" "error" 1
                    prepare_exec_status=1
                fi
            fi

            # 检查 alter 语法
            local check_status=$(check_alter_syntax "$line")
            if [ $check_status -ne 1 ];then
                log_message "sql 匹配失败: $line" "error" 1
                prepare_exec_status=1
            fi

            # 检查 not null
            local check_status=$(check_not_null_default_value "$line")
            if [ $check_status -ne 1 ];then
                log_message "not null 缺少默认值: $line" "error" 1
                prepare_exec_status=1
            fi

            local check_status=$(shunt_alter "$line"|tail -n 1|awk '{print $NF}')
            if [ $check_status -ne 1 ];then
                log_message "无法使用 pt-osc工具: $line" "warning" 1
                read -t 20  -e -p "是否继续(y/n)" Answer
                case $Answer in
                Y | y)
                    echo "确认继续"
                    ;;
                N | n)
                    echo "确认退出"
                    exit 1
                    ;;
                *)
                    echo "输入有误"
                    exit 1
                    ;;
                esac
            fi
            ;;
        BACKUP_TBALE)
            local table_name="$(sql_processing "table_name" "$line")"
            if [ "$table_name" == 'NULL' ];then
                log_message "表名提取失败: $line" "error"  1
                prepare_exec_status=1
            fi
            local table_status=$(check_table "$table_name")
            if [ $table_status -ne 1 ];then
                log_message "表不存在: $line" "error" 0
                prepare_exec_status=1
            fi
            ;;
        NULL)
            log_message "sql 类型无法识别: $line" "error"  1
            prepare_exec_status=1
            ;;
        *)
            log_message "sql 语法错误 无法提取表名: $line" "error" 1
            prepare_exec_status=1
            ;;
        esac

    done 3< ${MYSQL_GHOST_FILE}

    # 检查失败则退出
    echo $prepare_exec_status
    if [ $prepare_exec_status -eq 1 ];then
        exit 2
    fi
    log_message "备份检查已完成" "info" 0
}

# 输出查询数据为 xls
output() {
    # output "sql"
    local sql_info="$1"
    local table_name="$(sql_processing "table_name" "$sql_info")"

    $MYSQL_CONN $MYSQL_DATABASE -e "$sql_info" 2>${WORK_ORDER_LOG}   >>${OUTPUT_PATH}/${MYSQL_DATABASE}.${table_name}_${DATETIME}.xls
    if [ $? -ne 0 ];then
        echo 2
    else
        echo 1
    fi
}

# 执行备份
exec_backup(){
    local exec_backup_status=0
    log_message "备份中···" "info" 1
    while read -u 3 line ;do
        local sql_type=$(sql_processing "sql_type"  "$line")
        case $sql_type in
        SELECT_TABLE)
            log_message "查询语句 跳过备份" "info" 0 
            ;;
        INSERT_TBALE)
            log_message "插入语句 跳过备份" "info" 0
            ;;
        DELETE_TABLE)
            local table_name="$(sql_processing "table_name" "$line"|egrep  '\s*[a-zA-Z0-9_`]'|sort -n|uniq)"
            local where_info="$(sql_processing "condition" "$line")"

            if [ "$where_info" == 'NULL' ];then
                where_info=""
            fi

            local backup_status=$(mysql_backup "$table_name" "$where_info")

            if [[ $backup_status -ne 1 ]];then
                log_message "备份失败: $line" "error" 0
                exec_backup_status=1
            else
                log_message "${table_name} 表备份成功" "info" 0
            fi
            ;;

        UPDATE_TABLE)
            local table_name="$(sql_processing "table_name" "$line"|egrep  '\s*[a-zA-Z0-9_`]'|sort -n|uniq)"
            local where_info="$(sql_processing "condition" "$line")"

            if [ "$where_info" == 'NULL' ];then
                where_info=""
            fi

            local backup_status=$(mysql_backup "$table_name" "$where_info")

            if [[ $backup_status -ne 1 ]];then
                log_message "备份失败: $line" "error" 0
                exec_backup_status=1
            else
                log_message "${table_name} 表备份成功" "info" 0
            fi
            ;;

        CREATE_INDEX)
            local alter_sql="$(sql_change_create_index  "$line")"
            local table_name="$(sql_processing "table_name" "$alter_sql"|egrep  '\s*[a-zA-Z0-9_`]'|sort -n|uniq)"

            local backup_status=$(mysql_backup "$table_name" "")

            if [[ $backup_status -ne 1 ]];then
                log_message "备份失败: $line" "error" 0
                exec_backup_status=1
            else
                log_message "${table_name} 表备份成功" "info" 0
            fi
            ;;
        DROP_INDEX)
            local alter_sql="$(sql_change_drop_index  "$line")"
            local table_name="$(sql_processing "table_name" "$alter_sql"|egrep  '\s*[a-zA-Z0-9_`]'|sort -n|uniq)"
            local backup_status=$(mysql_backup "$table_name" "")

            if [[ $backup_status -ne 1 ]];then
                log_message "备份失败: $line" "error" 0
                exec_backup_status=1
            else
                log_message "${table_name} 表备份成功" "info" 0
            fi
            ;;
        CREATE_TABLE)
            log_message "建表语句 跳过备份" "info" 0
            ;;

        DROP_TABLE)
            local table_name="$(sql_processing "table_name" "$line"|egrep  '\s*[a-zA-Z0-9_`]'|sort -n|uniq)"
            local backup_status=$(mysql_backup "$table_name" "")

            if [[ $backup_status -ne 1 ]];then
                log_message "备份失败: $line" "error" 0
                exec_backup_status=1
            else
                log_message "${table_name} 表备份成功" "info" 0
            fi
            ;;
        TRUNCATE_TBALE)
            local table_name="$(sql_processing "table_name" "$line"|egrep  '\s*[a-zA-Z0-9_`]'|sort -n|uniq)"
            local backup_status=$(mysql_backup "$table_name" "")

            if [[ $backup_status -ne 1 ]];then
                log_message "备份失败: $line" "error" 0
                exec_backup_status=1
            else
                log_message "${table_name} 表备份成功" "info" 0
            fi
            ;;
        ALTER_TABLE)
            local table_name="$(sql_processing "table_name" "$line"|egrep  '\s*[a-zA-Z0-9_`]'|sort -n|uniq)"
            local backup_status=$(mysql_backup "$table_name" "")

            if [[ $backup_status -ne 1 ]];then
                log_message "备份失败: $line" "error" 0
                exec_backup_status=1
            else
                log_message "${table_name} 表备份成功" "info" 0
            fi
            ;;

        BACKUP_TBALE)
            local table_name="$(sql_processing "table_name" "$line"|egrep  '\s*[a-zA-Z0-9_`]'|sort -n|uniq)"
            local backup_status=$(mysql_backup "$table_name" "")

            if [[ $backup_status -ne 1 ]];then
                log_message "备份失败: $line" "error" 0
                exec_backup_status=1
            else
                log_message "${table_name} 表备份成功" "info" 0
            fi
            ;;
        *)
            log_message "sql 语法错误" "error" 0
            ;;
        esac

    done 3< ${MYSQL_GHOST_FILE}

    if [ $exec_backup_status -ne 0 ];then
        exit 2
    fi
    log_message "备份已完成" "info" 0
}

# 执行 SQL
exec_sql() {
    local exec_sql_status=0
    local alter_number=0
    while read -u 3 line; do
        let "alter_number++"
        local sql_type="$(sql_processing "sql_type" "$line")"
        let "alter_number++"
        case $sql_type in
            SELECT_TABLE)
                local exec_sql_status=$(output "$line")
                local table_name="$(sql_processing "table_name" "$line")"
                if [ $exec_sql_status -ne 1 ];then
                    log_message "导出文件失败: $line" "error" 0
                    exec_sql_status=1
                else
                    log_message "导出文件成功: ${OUTPUT_PATH}/${MYSQL_DATABASE}.${table_name}_${DATETIME}.xls" "info" 0
                fi
                ;;
            INSERT_TBALE|DELETE_TABLE|UPDATE_TABLE|CREATE_TABLE|DROP_TABLE|TRUNCATE_TBALE)
                $MYSQL_CONN ${MYSQL_DATABASE}  -N -vv  -e "$line"|fmt
                if [[ ${PIPESTATUS[0]} -ne 0 || ${PIPESTATUS[1]} -ne 0 ]];then
                    log_message "SQL 执行失败: $line" "error" 1
                    exec_sql_status=1
                fi
                ;;
            CREATE_INDEX)
                local alter_sql="$(sql_change_create_index "$line")"
                local check_alter=$(shunt_alter "$alter_sql"|tail -n 1|awk '{print $NF}')
                if [ $check_alter -ne 1 ];then
                    $MYSQL_CONN ${MYSQL_DATABASE}  -N -vv  -e "$line"|fmt
                    if [[ ${PIPESTATUS[0]} -ne 0 || ${PIPESTATUS[1]} -ne 0 ]];then
                        log_message "SQL 执行失败: $line" "error" 1
                        exec_sql_status=1
                    else
                        log_message "SQL 执行成功: $line" "info" 1
                    fi
                else
                    local status=$(exec_ptosc "$alter_sql" "${alter_number}")
                    if [ $status -eq 0 ];then
                        log_message "SQL 执行失败: $line" "error" 1
                        exec_sql_status=1
                    else
                        log_message "alter 正在执行···: ${line}" "info" 1
                        log_message "查看进度: tail -f ${LOG_PATH}/${DATETIME}_${alter_number}_alter.log"  "info" 1

                    fi
                fi
                ;;
            DROP_INDEX)
                local alter_sql="$(sql_change_drop_index "$line")"
                local check_alter=$(shunt_alter "$alter_sql"|tail -n 1|awk '{print $NF}')
                if [ $check_alter -ne 1 ];then
                    $MYSQL_CONN ${MYSQL_DATABASE}  -N -vv  -e "$line"|fmt
                    if [[ ${PIPESTATUS[0]} -ne 0 || ${PIPESTATUS[1]} -ne 0 ]];then
                        log_message "SQL 执行失败: $line" "error" 1
                        exec_sql_status=1
                    else
                        log_message "SQL 执行成功: ${line}" "info" 1
                        
                    fi
                else
                    local status=$(exec_ptosc "$alter_sql" "${alter_number}")
                    if [ $status -eq 0 ];then
                        log_message "SQL 执行失败: $line" "error" 1
                        exec_sql_status=1
                    else
                        log_message "alter 正在执行···: ${line}" "info" 1
                        log_message "查看进度: tail -f ${LOG_PATH}/${DATETIME}_${alter_number}_alter.log"  "info" 1
                    fi
                fi
                ;;
            ALTER_TABLE)
                local alter_sql="$line"
                local check_alter=$(shunt_alter "$alter_sql"|tail -n 1|awk '{print $NF}')
                if [ $check_alter -ne 1 ];then
                    $MYSQL_CONN ${MYSQL_DATABASE}  -N -vv  -e "$line"|fmt
                    if [[ ${PIPESTATUS[0]} -ne 0 || ${PIPESTATUS[1]} -ne 0 ]];then
                        log_message "SQL 执行失败: $line" "error" 1
                        exec_sql_status=1
                    else
                        log_message "SQL 执行成功: $line" "info" 1
                    fi
                else
                    local status=$(exec_ptosc "$line" "${alter_number}")
                    if [ $status -eq 0 ];then
                        log_message "SQL 执行失败: $line" "error" 1
                        exec_sql_status=1
                    else
                        log_message "alter 正在执行···: ${line}" "info" 1
                        log_message "查看进度: tail -f ${LOG_PATH}/${DATETIME}_${alter_number}_alter.log"  "info" 1

                    fi
                fi
                ;;
            BACKUP_TBALE)
                continue
                ;;
            *)
                log_message "exec_sql 类型错误无法执行" "error" 0
                ;;
        esac


    done 3< ${MYSQL_GHOST_FILE}

    if [ $exec_sql_status -ne 0 ];then
        exit 2
    fi

}

# 清理日志
clean_log() {
    cd  ${LOG_PATH} && find ./ -type f -name "1_work_order_*.log" -mtime +${LOG_SAVE_DAYES}  |xargs rm -f
    if [[ ${PIPESTATUS[0]} -ne 0 || ${PIPESTATUS[1]} -ne 0 ]];then
        log_message "日志清理失败" "error" 0
    fi
}

# 清理备份3
clean_backup() {
    cd ${BACKUP_PATH} && find ./ -type f -name "*-*\.*\.sql" -mtime +${BACKUP_SAVE_DAYES} |xargs rm -f
    if [[ ${PIPESTATUS[0]} -ne 0 || ${PIPESTATUS[1]} -ne 0 ]];then
        log_message "备份清理失败" "error" 0
    fi
}

# 总入口
main() {
    
    # 格式化 sql
    format_sql

    # 检查阶段
    prepare_exec

    # 备份阶段
    exec_backup

    # 执行阶段
    exec_sql

    # 清理阶段
    clean_log
    clean_backup

}

main